package edu;

import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.util.Vector;
import javax.swing.*;


public class Change extends JFrame {
    private static final long serialVersionUID = -1928970409928880648L;

    JLabel jlpno = new JLabel("工      号：");
    JLabel jlxiaoxue = new JLabel("小      学：");
    JLabel jlchuzhong = new JLabel("初      中：");
    JLabel jlgaozhong = new JLabel("高      中：");
    JLabel jldaxue = new JLabel("大      学：");
    JLabel jlshuoshi = new JLabel( "硕      士：");
    JLabel jlboshi = new JLabel("博      士：");

    JTextField jtpno = new JTextField("",20);
    JTextField jtxiaoxue = new JTextField("",20);
    JTextField jtchuzhong = new JTextField("",20);
    JTextField jtgaozhong = new JTextField("",20);
    JTextField jtdaxue = new JTextField("",20);
    JTextField jtshuoshi = new JTextField("",20);
    JTextField jtboshi = new JTextField("",20);

    JButton buttonchange = new JButton("修改");
    JButton buttonreturn = new JButton("返回");


    public Change() {
        JPanel jppno = new JPanel();
        JPanel jpxiaoxue = new JPanel();
        JPanel jpchuzhong = new JPanel();
        JPanel jpgaozhong = new JPanel();
        JPanel jpdaxue = new JPanel();
        JPanel jpshuoshi = new JPanel();
        JPanel jpboshi = new JPanel();


        JPanel jpforbutton = new JPanel(new GridLayout(1,1));

        jppno.add(jlpno);
        jppno.add(jtpno);

        jpxiaoxue.add(jlxiaoxue);
        jpxiaoxue.add(jtxiaoxue);


        jpchuzhong.add(jlchuzhong);
        jpchuzhong.add(jtchuzhong);

        jpgaozhong.add(jlgaozhong);
        jpgaozhong.add(jtgaozhong);



        jpdaxue.add(jldaxue);
        jpdaxue.add(jtdaxue);


        jpshuoshi.add(jlshuoshi);
        jpshuoshi.add(jtshuoshi);



        jpboshi.add(jlboshi);
        jpboshi.add(jtboshi);




        jpforbutton.add(buttonchange);
        jpforbutton.add(buttonreturn);

        buttonchange.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                boolean flag =false;
                flag= !jtpno.getText().equals("");

                System.out.println(flag);


                if ( !flag) {

                    if(e.getSource()==buttonchange){
                        javax.swing.JOptionPane.showMessageDialog(null, "未输入工号");
                    }

                }else {
                String xpno = jtpno.getText();
                String xxiaoxue = jtxiaoxue.getText();
                String xchuzhong = jtchuzhong.getText();
                String xgaozhong = jtgaozhong.getText();
                String xdaxue = jtdaxue.getText();
                String xshuoshi = jtshuoshi.getText();
                String xboshi = jtboshi.getText();

                Connection conn = null;
                Connection conn1 = null;
                Connection conn2 = null;
                ResultSet res = null;
                Statement stat = null;
                ResultSet res1 = null;
                ResultSet res2 = null;
                Statement stat1 = null;
                Statement stat2 = null;

                String sql = "SELECT pno FROM allstaff where pno='" + xpno + "' ;";
                String sql20 = "SELECT xw FROM (SELECT xx.et,xw FROM xx WHERE pno='" + xpno + "' UNION SELECT cz.et,xw FROM cz WHERE pno='" + xpno + "' UNION SELECT gz.et,xw FROM gz WHERE pno='" + xpno + "' UNION" +
                        " SELECT dx.et,xw FROM dx WHERE pno='" + xpno + "' UNION SELECT ss.et,xw FROM ss WHERE pno='" + xpno + "' UNION SELECT bs.et,xw FROM bs WHERE pno='" + xpno + "')AS b WHERE  et=(SELECT " +
                        " MAX(et) FROM (SELECT xx.et FROM xx WHERE pno='" + xpno + "' UNION SELECT cz.et FROM cz WHERE pno='" + xpno + "' UNION SELECT gz.et FROM gz WHERE pno='" + xpno + "' UNION" +
                        " SELECT dx.et FROM dx WHERE pno='" + xpno + "' UNION SELECT ss.et FROM ss WHERE pno='" + xpno + "' UNION SELECT bs.et FROM bs WHERE pno='" + xpno + "')AS total);;";
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    System.out.println("JBDC 加载成功!");
                } catch (Exception d) {
                    System.out.println("jdbc fall");
                    d.printStackTrace();
                }
                try {
                    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/work", "root", "123456");
                    conn1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/work", "root", "123456");
                    stat = conn.createStatement();
                    res = stat.executeQuery(sql);
                    while (res.next()) {
                        String i = res.getString(1).trim();
                        if (i.equals(jtpno.getText())) {
                            String sql2 = "UPDATE xx SET xxm='" + xxiaoxue + "'  WHERE pno='" + jtpno.getText() + "'";
                            String sql01 = "UPDATE xx SET xw='其他'  WHERE pno='" + jtpno.getText() + "'";
                            String sql5 = "UPDATE cz SET czm='" + xchuzhong + "'  WHERE pno='" + jtpno.getText() + "'";
                            String sql02 = "UPDATE cz SET xw='其他'  WHERE pno='" + jtpno.getText() + "'";
                            String sql8 = "UPDATE gz SET gzm='" + xgaozhong + "'  WHERE pno='" + jtpno.getText() + "'";
                            String sql03 = "UPDATE gz SET xw='其他'  WHERE pno='" + jtpno.getText() + "'";
                            String sql11 = "UPDATE dx SET dxm='" + xdaxue + "'  WHERE pno='" + jtpno.getText() + "'";
                            String sql04 = "UPDATE dx SET xw='本科'  WHERE pno='" + jtpno.getText() + "'";
                            String sql14 = "UPDATE ss SET ssm='" + xshuoshi + "'  WHERE pno='" + jtpno.getText() + "'";
                            String sql05 = "UPDATE ss SET xw='硕士'  WHERE pno='" + jtpno.getText() + "'";
                            String sql17 = "UPDATE bs SET bsm='" + xboshi + "'  WHERE pno='" + jtpno.getText() + "'";
                            String sql06 = "UPDATE bs SET xw='博士'  WHERE pno='" + jtpno.getText() + "'";
                            stat = conn.createStatement();
                            stat.executeUpdate(sql2);
                            stat.executeUpdate(sql01);
                            stat.executeUpdate(sql02);
                            stat.executeUpdate(sql03);
                            stat.executeUpdate(sql04);
                            stat.executeUpdate(sql05);
                            stat.executeUpdate(sql06);
                            stat.executeUpdate(sql5);
                            stat.executeUpdate(sql8);
                            stat.executeUpdate(sql11);
                            stat.executeUpdate(sql14);
                            stat.executeUpdate(sql17);
                            try {
                                stat.close();
                                conn.close();
                            } catch (SQLException ar) {
                                ar.printStackTrace();
                            }
                            break;
                        }
                    }
                }
                catch (SQLException e1) {
                    System.out.println("修改错误");
                    if(e.getSource()==buttonchange){
                        javax.swing.JOptionPane.showMessageDialog(null, "没有该工号，请重新输入");
                }e1.printStackTrace();} finally {
                    try {
                        conn.close();
                    } catch (SQLException ar) {
                        ar.printStackTrace();
                    }
                }
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    System.out.println("JBDC 加载成功!");
                } catch (Exception d) {
                    System.out.println("jdbc fall");
                    d.printStackTrace();
                }

                try {
                    conn1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/work", "root", "123456");
                    stat = conn1.createStatement();
                    stat1 = conn1.createStatement();
                    res1 = stat.executeQuery(sql);
                    while (res1.next()) {
                        String sql07 = "UPDATE xx SET et='"+"' WHERE xxm='"+"';";
                        String sql08 = "UPDATE cz SET et='"+"' WHERE czm='"+"';";
                        String sql09 = "UPDATE gz SET et='"+"' WHERE gzm='"+"';";
                        String sql010 = "UPDATE dx SET et='"+"' WHERE dxm='"+"';";
                        String sql011 = "UPDATE ss SET et='"+"' WHERE ssm='"+"';";
                        String sql012 = "UPDATE bs SET et='"+"' WHERE bsm='"+"';";
                        stat1 = conn1.createStatement();
                        stat1.executeUpdate(sql07);
                        stat1.executeUpdate(sql08);
                        stat1.executeUpdate(sql09);
                        stat1.executeUpdate(sql010);
                        stat1.executeUpdate(sql011);
                        stat1.executeUpdate(sql012);
                        try {
                            stat1.close();
                            conn1.close();
                        } catch (SQLException ar) {
                            ar.printStackTrace();
                        }
                        break;
                    }
                }
                catch (SQLException e1) {
                    e1.printStackTrace();
                } finally {
                    try {
                        conn.close();
                    } catch (SQLException ar) {
                        ar.printStackTrace();
                    }
                }
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    System.out.println("JBDC 加载成功!");
                } catch (Exception d) {
                    System.out.println("jdbc fall");
                    d.printStackTrace();
                }

                try {
                    conn2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/work", "root", "123456");
                    stat = conn2.createStatement();
                    stat1 = conn2.createStatement();
                    res2 = stat.executeQuery(sql20);
                    while (res2.next()) {
                        String sql013 = "UPDATE allstaff SET xueli='"+res2.getString("xw")+"' WHERE pno='" + jtpno.getText() + "'";
                        stat2 = conn2.createStatement();
                        stat2.executeUpdate(sql013);
                        try {
                            stat1.close();
                            conn1.close();
                        } catch (SQLException ar) {
                            ar.printStackTrace();
                        }
                        break;
                    }
                }
                catch (SQLException e1) {
                    e1.printStackTrace();
                } finally {
                    try {
                        conn.close();
                    } catch (SQLException ar) {
                        ar.printStackTrace();
                    }
                }}
            }});

        buttonreturn.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e){
                dispose();
            }
        });
        this.setTitle("修改员工毕业院校");
        this.setLayout(new GridLayout(9,1));
        this.add(jppno);
        this.add(jpxiaoxue);
        this.add(jpchuzhong);
        this.add(jpgaozhong);
        this.add(jpdaxue);
        this.add(jpshuoshi);
        this.add(jpboshi);
        this.add(jpforbutton);
        this.setLocation(400,300);
        this.setSize(350,300);
        this.setVisible(true);
    }
}
